package com.song.dao.impl;

import com.song.bean.Student;
import com.song.dao.StudentDao;
import com.song.dao.Utils;
import com.song.util.StudentEnum;


import javax.enterprise.inject.New;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**获取学生信息
 * @author List<Student>学生集合
 */
public class StudentDaoImpl extends Utils implements StudentDao {
    @Override
    public List<Student> getStudent(String stuname,String stuno,int sex,int pageindex,int pagesize) {
        //学生集合
        List<Student> students = new ArrayList<Student>();
        List<Object> list = new ArrayList<>();
        //模糊查找
        StringBuffer buffer = new StringBuffer("select * from student where 1=1");
        if (stuname != null&&stuname.length() >0){
            buffer.append(" and stuname like ?");
            list.add("%" + stuname + "%");
        }
        if (stuno != null&&stuno.length() >0){
            buffer.append(" and stuno = ?");
            list.add(stuno);
        }if (sex != -1){
            buffer.append(" and sex = ?");
            list.add(sex);
        }
        //分页查找
        buffer.append(" limit ?,?");
        list.add((pageindex-1)*pagesize);
        list.add(pagesize);

        resultSet = select(buffer.toString(), list);

        try {

            while (resultSet.next()) {
                Student student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                student.setStuno(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setIdnumber(resultSet.getString("idnumber"));
                student.setProfession(resultSet.getString("profession"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                students.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return students;
    }
    //查询学生信息总数
    @Override
    public Integer getTotalStudent(String stuname, String stuno, int sex) {
        int total = 0;
        List<Object> list = new ArrayList<>();
        //模糊查找
        StringBuffer buffer = new StringBuffer("select count(*) from student where 1=1");
        if (stuname != null&&stuname.length() >0){
            buffer.append(" and stuname like ?");
            list.add("%" + stuname + "%");
        }
        if (stuno != null&&stuno.length() >0){
            buffer.append(" and stuno = ?");
            list.add(stuno);
        }if (sex != -1){
            buffer.append(" and sex = ?");
            list.add(sex);
        }

        resultSet = select(buffer.toString(), list);
        try {
            while (resultSet.next()) {
                total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return total;
    }
    //添加学生
    @Override
    public Integer insertStudent(Student student) {
        int count = 0;
        try {
            String sql = "insert into student values (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List<Object> list = new ArrayList<>();
            list.add(student.getStuname());
            list.add(student.getStuno());
            list.add(student.getSex());
            list.add(student.getPhone());
            list.add(student.getEmail());
            list.add(student.getRegistered());
            list.add(student.getAddress());
            list.add(student.getProfession());
            list.add(student.getIdnumber());
            list.add(student.getPolitics());
            list.add(new Date());
            list.add(StudentEnum.READINGS.type);
            list.add(student.getIntroduction());
            list.add(student.getGid());
            count = update(sql, list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return count;
    }

    @Override
    public Student findById(Integer stuid) {
        Student student = new Student();
        List<Object> list = new ArrayList<>();
        try {
            String sql = "select * from student where stuid=?";
            list.add(stuid);
            resultSet = select(sql, list);
            while (resultSet.next()) {
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                student.setStuno(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setIdnumber(resultSet.getString("idnumber"));
                student.setProfession(resultSet.getString("profession"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return student;
    }

    @Override
    public Integer updatetudent(Student student) {
        int count = 0;
        try {
            String sql = "update student set stuname=?,stuno=?,sex=?,phone=?,email=?,registered=?,address=?," +
                    "profession=?,idnumber=?,politics=?,regdate=?,state=?,introduction=?,gid=? where stuid=?";
            List<Object> list = new ArrayList<>();
            list.add(student.getStuname());
            list.add(student.getStuno());
            list.add(student.getSex());
            list.add(student.getPhone());
            list.add(student.getEmail());
            list.add(student.getRegistered());
            list.add(student.getAddress());
            list.add(student.getProfession());
            list.add(student.getIdnumber());
            list.add(student.getPolitics());
            list.add(new Date());
            list.add(1);
            list.add(student.getIntroduction());
            list.add(student.getGid());
            list.add(student.getStuid());
            count = update(sql, list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return count;
    }

    @Override
    public Integer deleteStudent(Integer stuid) {
        int delete = 0;
        try {
            String sql = "DELETE FROM student where stuid = ?";
            List<Object> list = new ArrayList<>();
            list.add(stuid);
            delete = update(sql, list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return delete;
    }

    @Override
    public Boolean stunoIsExist(String stuno) {
        try {
            String sql = "SELECT stuno from student where stuno=?";
            List<Object> list = new ArrayList<>();
            list.add(stuno);
            resultSet = select(sql, list);
            while (resultSet.next()) {
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return true;
    }
}
